package com.infore.dataAudit.util.excel;

import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.util.CellRangeAddress;

import com.infore.dataAudit.util.excel.entity.ExcelEntity;
import com.infore.dataAudit.util.excel.entity.ExcelTd;
import com.infore.dataAudit.util.excel.entity.ExcelTr;



public class ExcelUtil {
    private static FormulaEvaluator evaluator;

    /** 导出 **/
    public static byte[] exportExcelEntity(ExcelEntity en) {
        // StringBuilder sb = new StringBuilder();
        byte[] bytes = null;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel工作簿
            evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            HSSFSheet sheet = workbook.createSheet("sheet0");// 创建sheet
            HSSFCellStyle headStyle = createFieldStyle(workbook);// 表头样式
            HSSFCellStyle subTitleStyle = createTitleAlRightStyle(workbook);//
            HSSFCellStyle dataStyle = createDataStyle(workbook);// 数据样式
            CellRangeAddress cra = null;
            List<ExcelTr> head = en.getHead();// 表头数据
            List<ExcelTr> body = en.getBody();
            String text = "";
            int rowIndex = 0;
            int colIndex = -1;
            HSSFCell cell = null;
            HSSFRow row = null;

            int colSize = en.getColSize();
            int headRowSiza = 0;
            String title = en.getTitle();
            String subTitle = en.getSubTitle();
            row = sheet.createRow(rowIndex);
            row.setHeight((short) (40 * 20));
            cra = new CellRangeAddress(0, 0, 0, colSize - 1);
            sheet.addMergedRegion(cra);
            cell = row.createCell(0);
            cell.setCellValue(title);
            cell.setCellStyle(headStyle);
            
            if(subTitle != null ) {
                rowIndex++;
                row = sheet.createRow(rowIndex);
                row.setHeight((short) (40 * 20));
                cra = new CellRangeAddress(1, 1, 0, colSize - 1);
                sheet.addMergedRegion(cra);
                cell = row.createCell(0);
                cell.setCellValue(subTitle);
                cell.setCellStyle(subTitleStyle);
            }

            if (head != null) {
                headRowSiza = head.size();
                int titleRow = rowIndex;
                for (ExcelTr tr : head) {
                    rowIndex++;
                    row = sheet.createRow(rowIndex);

                    row.setHeight((short) (tr.getHeight() * 20));
                    List<ExcelTd> ths = tr.getTds();//
                    colIndex = -1;

                    int colWidth = 21;
                    for (ExcelTd th : ths) {
                        colIndex++;
                        if (th != null) {
                            int colspan = th.getColspan();
                            colWidth = th.getWidth();
                            cell = row.createCell(colIndex);
                            text = th.getText();
                            int rowspan = th.getRowspan();
                            cra = new CellRangeAddress(rowIndex, rowIndex + rowspan - 1, colIndex,
                                    colIndex + colspan - 1);
                            sheet.addMergedRegion(cra);
                            cell.setCellValue(text);
                            cell.setCellStyle(headStyle);

                            // sheet.setColumnWidth(colIndex, th.getWidth());
                            if (rowspan > 1) {
                                sheet.setColumnWidth(colIndex, colWidth * 256);
                            } else {
                                if (rowIndex == (headRowSiza + titleRow)) {
                                    sheet.setColumnWidth(colIndex, colWidth * 256);
                                }
                            }
                            colIndex = colIndex + colspan - 1;
                        }

                    }
                }
            }

            if (body != null) {
                for (ExcelTr tr : body) {
                    rowIndex++;
                    row = sheet.createRow(rowIndex);
                    row.setHeight((short) (tr.getHeight() * 20));
                    List<ExcelTd> ths = tr.getTds();//
                    colIndex = -1;
                    for (ExcelTd td : ths) {
                        colIndex++;
                        cell = row.createCell(colIndex);
                        text = td.getText();

                        cell.setCellValue(text);
                        cell.setCellStyle(dataStyle);

                    }
                }
            }

            // String path =
            // req.getSession().getServletContext().getRealPath("/export/"+fileName+".xls");
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            // File file = new File(path+"/export/"+fileName+".xls");
            // FileOutputStream fos;

            // fos = new FileOutputStream(file);
            workbook.write(baos);
            // fos.
            baos.flush();
            bytes = baos.toByteArray();
            baos.close();
            // fos.flush();
            // fos.close();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return bytes;
    }

    /**
     * 创建表头单元格样式
     *
     * @param wb
     * @return
     * @Date 2017年7月6日 下午6:06:15
     * @Author renyu
     */
    private static HSSFCellStyle createFieldStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont(); // 创建字体
        curFont.setFontName("宋体"); // 设置字体格式
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setFontHeightInPoints((short) 16); // 字体大小
        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
        cellStyle.setFont(curFont); // 字体加入样式
        // cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); //粗实线
        // cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //实线
        // cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); //比较粗实线
        // cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //实线
        // cellStyle.setWrapText(true); //换行
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 横向居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中
        // cellStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);

        return cellStyle;
    }

    /**
     * @param wb
     * @return
     * @Description: 右对齐
     * @date 2017年11月22日
     * @author cys
     */
    private static HSSFCellStyle createTitleAlRightStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont(); // 创建字体
        curFont.setFontName("宋体"); // 设置字体格式
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setFontHeightInPoints((short) 12); // 字体大小
        // curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
        cellStyle.setFont(curFont); // 字体加入样式
        // cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); //粗实线
        // cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //实线
        // cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); //比较粗实线
        // cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //实线
        // cellStyle.setWrapText(true); //换行
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 横向居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中
        // cellStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);

        return cellStyle;
    }

    /**
     * 创建数据单元格样式
     *
     * @param wb
     * @return
     * @Date 2017年7月7日 上午11:33:32
     * @Author renyu
     */
    private static HSSFCellStyle createDataStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont(); // 创建字体
        curFont.setFontName("宋体"); // 设置字体格式
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setFontHeightInPoints((short) 14);
        cellStyle.setFont(curFont);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 横向居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中
        cellStyle.setWrapText(true);
        return cellStyle;
    }


    /**
     * 获取审核日志表头
     * 
     * @author 蔡永顺
     * @return List<ExcelTr>
     * @return
     * @date 2019年6月5日上午11:29:16
     */
    public static List<ExcelTr> getAuditRecordHead() {
        List<ExcelTr> head = new ArrayList<>();
        ExcelTr row = new ExcelTr();
        head.add(row);
        List<ExcelTd> ths = new ArrayList<>();
        row.setTds(ths);
        ExcelTd th = new ExcelTd();
        th.setText("序号");
        th.setWidth(10);
        ths.add(th);

        th = new ExcelTd();
        th.setText("省份");
        th.setWidth(25);
        th.setFileName("stationProvin");
        ths.add(th);

        th = new ExcelTd();
        th.setText("地市");
        th.setWidth(25);
        th.setFileName("stationCity");
        ths.add(th);

        th = new ExcelTd();
        th.setText("运维单位");
        th.setWidth(45);
        th.setFileName("stationCompany");
        ths.add(th);

        th = new ExcelTd();
        th.setText("点位名称");
        th.setWidth(25);
        th.setFileName("stationName");
        ths.add(th);

        th = new ExcelTd();
        th.setText("监测时间");
        th.setWidth(25);
        th.setFileName("stationDate");
        ths.add(th);

        th = new ExcelTd();
        th.setText("审核操作");
        th.setWidth(25);
        th.setFileName("auditOperation");
        ths.add(th);

        th = new ExcelTd();
        th.setText("审核人");
        th.setWidth(25);
        th.setFileName("auditUser");
        ths.add(th);

        th = new ExcelTd();
        th.setText("审核时间");
        th.setWidth(25);
        th.setFileName("auditDate");
        ths.add(th);

        th = new ExcelTd();
        th.setText("审核原因");
        th.setWidth(25);
        th.setFileName("auditReason");
        ths.add(th);

        return head;
    }
}
